package com.lanou.utp.service.impl;

import com.lanou.utp.entity.ResponseData;
import com.lanou.utp.entity.Student;
import com.lanou.utp.entity.Teacher;
import com.lanou.utp.entity.User;
import com.lanou.utp.mapper.StudentMapper;
import com.lanou.utp.mapper.TeacherMapper;
import com.lanou.utp.mapper.UserMapper;
import com.lanou.utp.service.ExcelService;
import com.lanou.utp.utils.ExcelUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;


@Service("excelService")
public class ExcelServiceImpl implements ExcelService {

    @Resource
    private StudentMapper studentMapper;
    @Resource
    private TeacherMapper teacherMapper;
    @Resource
    private UserMapper userMapper;


    //学生表格信息下载
    @Override
    @Transactional
    public void outputStudent(HttpServletResponse response) {
        List<Student> students = studentMapper.selectAllStu();
        String[] header = {"学号", "姓名", "手机号", "学院", "专业", "班级","学院编号","专业编号","班级编号"};
        String sheetName = "学生";
        String fileName = "学生信息表" + ".xlsx";
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = ExcelUtils.createSheet(workbook, sheetName, header);
        sheet.setColumnWidth(4, 5000);
        SXSSFRow row = null;
        int rowNumber = 1;
        for (Student student : students) {
            row = sheet.createRow(rowNumber);
            row.createCell(0).setCellValue(student.getUser().getNo());
            row.createCell(1).setCellValue(student.getUser().getName());
            row.createCell(2).setCellValue(student.getUser().getTel());
            row.createCell(3).setCellValue(student.getCollegeName());
            row.createCell(4).setCellValue(student.getMajorName());
            row.createCell(5).setCellValue(student.getClazzName());
            row.createCell(6).setCellValue(student.getCollegeId());
            row.createCell(7).setCellValue(student.getMajorId());
            row.createCell(8).setCellValue(student.getClazzId());
            rowNumber++;
        }
        ExcelUtils.excelOutput(response,fileName,workbook);
    }


    //教师表格信息下载
    @Override
    @Transactional
    public void outputTeacher(HttpServletResponse response) {
        List<Teacher> teachers = teacherMapper.selectAllTeachers();
        String[] header = {"工号", "姓名", "手机号", "教师类型", "学院", "专业", "基地","学院编号","专业编号","基地编号"};
        String sheetName = "教师";
        String fileName = "教师信息表" + ".xlsx";
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = ExcelUtils.createSheet(workbook, sheetName, header);
        sheet.setColumnWidth(4, 5000);
        SXSSFRow row = null;
        int rowNumber = 1;
        for (Teacher teacher : teachers) {
            row = sheet.createRow(rowNumber);
            row.createCell(0).setCellValue(teacher.getUser().getNo());
            row.createCell(1).setCellValue(teacher.getUser().getName());
            row.createCell(2).setCellValue(teacher.getUser().getTel());
            if (teacher.getType() == 1){
                row.createCell(3).setCellValue("校外");
            }else {
                row.createCell(3).setCellValue("校内");
            }
            row.createCell(4).setCellValue(teacher.getCollegeName());
            row.createCell(5).setCellValue(teacher.getMajorName());
            row.createCell(6).setCellValue(teacher.getBaseName());
            if(teacher.getCollegeId() == null){
                row.createCell(7).setCellValue("");
                row.createCell(8).setCellValue("");
            }else {
                row.createCell(7).setCellValue(teacher.getCollegeId());
                row.createCell(8).setCellValue(teacher.getMajorId());
            }
            if (teacher.getBaseId()==null){
                row.createCell(9).setCellValue("");
            }else {
                row.createCell(9).setCellValue(teacher.getBaseId());
            }
            rowNumber++;
        }
        ExcelUtils.excelOutput(response,fileName,workbook);
    }


    @Override
    @Transactional
    public ResponseData inputStudentModel(MultipartFile file) throws IOException{

        // **用WorkbookFactory来解决不同版本的Excel文件生成Workbook
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(file.getInputStream());
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            return new ResponseData(400,"文件上传错误");
        }
        //XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i < lastRowNum + 1; i++) {
            Student student = new Student();
            User user = new User();
                sheet.getRow(i).getCell(0).setCellType(CellType.STRING);
            user.setNo(sheet.getRow(i).getCell(0).getStringCellValue());
            user.setName(sheet.getRow(i).getCell(1).getStringCellValue());
                sheet.getRow(i).getCell(2).setCellType(CellType.STRING);
            user.setTel(sheet.getRow(i).getCell(2).getStringCellValue());
            //System.out.println("user========="+user);
            userMapper.addStudent(user);
            Long id = user.getId();
            student.setId(id);
            //System.out.println("CollegeId========="+((long) sheet.getRow(i).getCell(6).getNumericCellValue()));
            student.setCollegeId((long) sheet.getRow(i).getCell(6).getNumericCellValue());
            //System.out.println("MajorId=========="+((long) sheet.getRow(i).getCell(7).getNumericCellValue()));
            student.setMajorId((long) sheet.getRow(i).getCell(7).getNumericCellValue());
            //System.out.println("ClazzId=========="+((long) sheet.getRow(i).getCell(8).getNumericCellValue()));
            student.setClazzId((long) sheet.getRow(i).getCell(8).getNumericCellValue());
            studentMapper.addStudent(student);
        }
        return new ResponseData("ok");
    }

    @Override
    public ResponseData inputTeacherModel(MultipartFile file) throws IOException {

        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(file.getInputStream());
        } catch (InvalidFormatException e) {
            e.printStackTrace();
            return new ResponseData(400,"文件上传错误");
        }
        Sheet sheet = workbook.getSheetAt(0);
        int lastRowNum = sheet.getLastRowNum();
        for (int i = 1; i < lastRowNum + 1; i++) {
            Teacher teacher = new Teacher();
            User user = new User();
            //"工号", "姓名", "手机号", "教师类型", "学院", "专业", "基地","7学院编号","8专业编号","9基地编号"
                sheet.getRow(i).getCell(0).setCellType(CellType.STRING);
            user.setNo(sheet.getRow(i).getCell(0).getStringCellValue());
            user.setName(sheet.getRow(i).getCell(1).getStringCellValue());
                sheet.getRow(i).getCell(2).setCellType(CellType.STRING);
            user.setTel(sheet.getRow(i).getCell(2).getStringCellValue());
            userMapper.addTeacher(user);
            Long id = user.getId();
            teacher.setId(id);
            teacher.setCollegeId((long) sheet.getRow(i).getCell(7).getNumericCellValue());
            teacher.setMajorId((long) sheet.getRow(i).getCell(8).getNumericCellValue());
            teacher.setBaseId((long) sheet.getRow(i).getCell(9).getNumericCellValue());
            teacherMapper.addTeacher(teacher);
        }
        return new ResponseData("ok");
    }
}
